<?php
/** 
 * collection of properties and methods for the sr23 class
 */

class sr23_pdo {
    public $sr23a_keys = array(
        'NDB_No', 
        'Shrt_Desc',
        'Water',
        'Energ_Kcal',
        'Protein',
        'Lipid_Tot',
        'Ash',
        'Carbohydrt',
        'Fiber_TD',
        'Sugar_Tot',
        'Calcium',
        'Iron',
        'Magnesium',
        'Phosphorus',
        'Potassium',
        'Sodium',
        'Zinc',
        'Copper',
        'Manganese',
        'Selenium',
        'Vit_C',
        'Thiamin',
        'Riboflavin',
        'Niacin',
        'Panto_acid',
        'vit_B6',
        'Folate_Tot',
        'Folic_acid',
        'Food_Folate',
        'Folate_DFE',
        'Choline_Tot',
        'Vit_B12',
        'Vit_A_IU',
        'Vit_A_RAE',
        'Retinol',
        'Alpha_Carot',
        'Beta_Carot',
        'Beta_Crypt',
        'Lycopene',
        'Lut_Zea',
        'Vit_E',
        'Vit_D_mcg',
        'Vit_D_IU',
        'Vit_K',
        'FA_Sat',
        'FA_Mono',
        'FA_Poly',
        'Cholestrl',
        'GmWt_1',
        'GmWt_Desc1',
        'GmWt_2',
        'GmWt_Desc2',
        'Refuse_Pct');

    public $sr23fd_keys = array(
        'NDB_No',
        'FdGrp_Cd',
        'Long_Desc',
        'Shrt_Desc',
        'ComName',
        'ManufacName',
        'Survey',
        'Ref_desc',
        'Refuse',
        'SciName',
        'N_Factor',
        'Pro_Factor',
        'Fat_Factor',
        'CHO_Factor');

    public $create_abbreviated = "
        create table abbreviated (
        NDB_No varchar(10), 
        Shrt_Desc varchar(60),
        Water varchar(25),
        Energ_Kcal varchar(25),
        Protein varchar(25),
        Lipid_Tot varchar(25),
        Ash varchar(25),
        Carbohydrt varchar(25),
        Fiber_TD varchar(25),
        Sugar_Tot varchar(25),
        Calcium varchar(25),
        Iron varchar(25),
        Magnesium varchar(25),
        Phosphorus varchar(25),
        Potassium varchar(25),
        Sodium varchar(25),
        Zinc varchar(25),
        Copper varchar(25),
        Manganese varchar(25),
        Selenium varchar(25),
        Vit_C varchar(25),
        Thiamin varchar(25),
        Riboflavin varchar(25),
        Niacin varchar(25),
        Panto_acid varchar(25),
        vit_B6 varchar(25),
        Folate_Tot varchar(25),
        Folic_acid varchar(25),
        Food_Folate varchar(25),
        Folate_DFE varchar(25),
        Choline_Tot varchar(25),
        Vit_B12 varchar(25),
        Vit_A_IU varchar(25),
        Vit_A_RAE varchar(25),
        Retinol varchar(25),
        Alpha_Carot varchar(25),
        Beta_Carot varchar(25),
        Beta_Crypt varchar(25),
        Lycopene varchar(25),
        Lut_Zea varchar(25),
        Vit_E varchar(25),
        Vit_D_mcg varchar(25),
        Vit_D_IU varchar(25),
        Vit_K varchar(25),
        FA_Sat varchar(25),
        FA_Mono varchar(25),
        FA_Poly varchar(25),
        Cholestrl varchar(25),
        GmWt_1 varchar(25),
        GmWt_Desc1 varchar(20),
        GmWt_2 varchar(25),
        GmWt_Desc2 varchar(20),
        Refuse_Pct varchar(3), primary key (NDB_No)) ENGINE=InnoDB DEFAULT CHARSET=Latin1;
";

    public $create_food_description = "
        CREATE TABLE food_description (
        NDB_No varchar(10),
        FdGrp_Cd varchar(10),
        Long_Desc varchar(200),
        Shrt_Desc varchar(60),
        ComName varchar(100),
        ManufacName varchar(65),
        Survey varchar(10),
        Ref_desc varchar(135),
        Refuse varchar(20),
        SciName varchar(65),
        N_Factor varchar(70),
        Pro_Factor varchar(70),
        Fat_Factor varchar(70),
        CHO_Factor varchar(70), primary key (NDB_No)) ENGINE=InnoDB DEFAULT CHARSET=Latin1;
";
    public $dbh;   
    
    /**
     * the main PDO method, that outputs the PDO object to be used repeatedly in this application
     */
    public function __construct() {
        // $dbuser = 'letidc5_sci';
        // $dbpass = '+5ursD@Y';
        require_once "config.php";
        // $dbh = new PDO('mysql:dbname=letidc5_sr23;host=localhost', $dbuser, $dbpass);
        $this->dbh = new PDO($connection, $dbuser, $dbpass);
    }

    /**
     * this should only be run once
     */
    public function create_tables() {
        // check if we can create the dbh
        if ($this->dbh) {
            // create an array of responses
            $responses = array();
            // if yes, we want to create the abbreviated table first
            try
            {
                $sth = $this->dbh->prepare($this->create_abbreviated);
                $sth->execute();
                array_push($responses, 'successfully created abbreviated table.');
            }
            catch (PDOException $e) {
                array_push($responses, $e->getMessage());
            }
            
            // then create the food_description table
            try
            {
                $sth = $this->dbh->prepare($this->create_food_description);
                $sth->execute();
                array_push($responses, 'successfully created food description table.');
            }
            catch (PDOException $e) {
                array_push($responses, $e->getMessage());

            }
            return $responses;
        }
    }
    
    /**
     * this should only be run once
     * use this only when using sqlite3 and transactions
     */
    public function populate_abbreviated_table_sqlite() {
        $this->dbh->beginTransaction(); // begin a SQL transaction

        // scan the abbreviated table and for each line, enter in as row in abbreviated table
        $sr23_af = file('ABBREV.txt');

        // start the keys and values
        $keys = '';
        $values = ''; 

        foreach ($this->sr23a_keys as $k) {
            $keys .= "$k, ";
            $values .= "?, ";
        }
        $keys = substr($keys, 0, -2); // get rid of last comma
        $values = substr($values, 0, -2); // get rid of last comma

        $sql_af = "insert into abbreviated ($keys) values ($values)";
        $sth = $this->dbh->prepare($sql_af); // prepare the statement for execution
        // print $sql_af;

        // read through each line in the $sr23_af array and plug into hash
        foreach ($sr23_af as $line) {
            $line_array = explode('^',$line);
            $sth->execute($line_array);  
        }

        try
        {
            $this->dbh->commit();
            print "<p>Transaction Committed Successfully</p>";
        }
        catch(PDOException $e)
        {
            print "<p>" . $e->getMessage() . "</p>";
        }
    }

    /**
     * this should ony be run once
     * use this only when using sqlite3 and transactions
     */
    public function populate_food_description_table_sqlite() {
        $this->dbh->beginTransaction(); // begin a SQL transaction

        // scan the abbreviated table and for each line, enter in as row in abbreviated table
        $sr23_fdf = file('FOOD_DES.txt');

        // start the keys and values
        $keys = '';
        $values = ''; 

        foreach ($this->sr23fd_keys as $k) {
            $keys .= "$k, ";
            $values .= "?, ";
        }
        $keys = substr($keys, 0, -2); // get rid of last comma
        $values = substr($values, 0, -2); // get rid of last comma

        $sql_fdf = "insert into food_description ($keys) values ($values)";
        $sth = $this->dbh->prepare($sql_fdf); // prepare the statement for execution
        // print $sql_af;

        // read through each line in the $sr23_af array and plug into hash
        foreach ($sr23_fdf as $line) {
            $line_array = explode('^',$line);
            $sth->execute($line_array);  
        }

        try
        {
            $this->dbh->commit();
            print "<p>Transaction Committed Successfully</p>";
        }
        catch(PDOException $e)
        {
            print "<p>" . $e->getMessage() . "</p>";
        }
    }
    
    /**
     * this should only be run once
     * use this only when using mysql
     */
    public function populate_abbreviated_table_mysql() {
        $this->dbh->beginTransaction();
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // scan the abbreviated table and for each line, enter in as row in abbreviated table
        $sr23_af = file('ABBREV.txt');
        // print count($sr23_af);
        // exit();

        // start the keys and values
        $keys = '';
        $values = ':'; 

        foreach ($this->sr23a_keys as $k) {
            $keys .= "$k, ";
            $values .= "$k, :";
        }
        $keys = substr($keys, 0, -2); // get rid of last comma
        $values = substr($values, 0, -3); // get rid of last comma and colon

        $sql_af = "insert into abbreviated ($keys) values ($values)";
        $sth = $this->dbh->prepare($sql_af); // prepare the statement for execution
        // die($sql_af);

        // read through each line in the $sr23_af array and plug into hash
        foreach ($sr23_af as $line) {
            $line_array = explode('^',$line);
            for ($i=0;$i<count($line_array);$i++) {
                $sth->bindValue(":" .$this->sr23a_keys[$i], $line_array[$i], PDO::PARAM_STR);
            }
            $sth->execute();
        }
        $this->dbh->commit();
    }

    
    /**
     * this should ony be run once
     * use this only when using mysql
     */
    public function populate_food_description_table_mysql() {
        $this->dbh->beginTransaction();
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // scan the abbreviated table and for each line, enter in as row in abbreviated table
        $sr23_fdf = file('FOOD_DES.txt');
        // print count($sr23_fdf);
        // exit();

        // start the keys and values
        $keys = '';
        $values = ':'; 

        foreach ($this->sr23fd_keys as $k) {
            $keys .= "$k, ";
            $values .= "$k, :";
        }
        $keys = substr($keys, 0, -2); // get rid of last comma
        $values = substr($values, 0, -3); // get rid of last comma and colon

        $sql_fdf = "insert into food_description ($keys) values ($values)";
        $sth = $this->dbh->prepare($sql_fdf); // prepare the statement for execution
        // die($sql_fdf);

        // read through each line in the $sr23_af array and plug into hash
        foreach ($sr23_fdf as $line) {
            $line_array = explode('^',$line);         
            for ($i=0;$i<count($line_array);$i++) {
                $sth->bindValue(":" . $this->sr23fd_keys[$i],$line_array[$i], PDO::PARAM_STR);
            }
            $sth->execute();  
        }
        $this->dbh->commit();
    }
    
    
    /**
     * above is end of initial set-up
     * below are methods that will be used repeatedly by the application
     */
    
    // function to output nutrient object w/o calculation
    public function print_nutrient($ndbno) {
        $query = "select * from abbreviated where NDB_No = '~$ndbno~'";      
        $sth = $this->dbh->prepare($query);
        $sth->execute();

        $result = $sth->fetchAll(PDO::FETCH_ASSOC);
        if (count($result) > 0) {
            print "<p>Match for $ndbno:</p>";
            print '<pre>';
            print_r($result);
            print '</pre>';
        }
        else
        {
            print "<p>$ndbno has no records in the database</p>";
        }
    }

    // function to output nutrient object after calculation
    public function calculate_nutrient_from_weight($ndbno, $weight, $sr23a_keys) {
        $nutrient = array(); // set the empty array to return

        $query = "select * from abbreviated where NDB_No = '$ndbno'";
        $sth = $this->dbh->prepare($query);
        $sth->execute();

        $result = $sth->fetchAll();
        // die(print_r($result));
        // you want items 2 .. 47
        for ($i = 2;$i < 48; $i++) {
            // calculate
            // VH = (N * CM) / 100
            // VH: The nutrient content per the desired common measure
            // N: The nutrient content per 100g
            // For NDB_No 01001, fat = 81.11g/100g
            // CM: grams of the common measure
            // for NDB_No 01001, 1 tablespoon = 14.2g
            // so, using this formula for the above example:
            // VH = (81.11 * 14.2) / 100 = 11.52g of fat in 1 tablespoon of butter
            $VH = ($result[0][$i] * $weight) / 100;
            // array_push($nutrient, $VH);
            $nutrient[$sr23a_keys[$i]] = $VH;
        }
        
        foreach (array('NDB_No','Shrt_Desc','GmWt_1','GmWt_Desc1','GmWt_2','GmWt_Desc2') as $a) {
            $nutrient[$a] = $result[0][$a];
        }
        
        return $nutrient;
    }

    /**
     * takes a string of desc to match against the db
     * depending on the nature of desc, the function would return either a pretty print array or string
     */
    public function match_description($desc) {
        // check to see if search description has quotes, if so, search as single words, otherwise do a search for each word
        // TODO: match against multiples in various order, i.e. "milk chocolate" could be "milk * chocolate", "milk chocolate", "chocolate milk" or "chocolate * milk"
        if (preg_match("/^\"/", $desc, $matches)) {
            // strip the quotes
            $desc = str_replace('"','',$desc); // get rid of quotes
            // die($desc);
            // single word search
            $query = "select NDB_No, Long_Desc from food_description where Long_Desc like '%$desc%' or Long_Desc like '%$desc' or Long_Desc like '$desc%'";   
            $sth = $this->dbh->prepare($query);
            $sth->execute();
            $results = $sth->FetchAll(PDO::FETCH_ASSOC);
            
            if (count($results) > $_POST['show_max']) {
                print '<p>there are more than ' . $_POST['show_max'] . ' matches (' . count($results) . ' found).</p>';
            }
            else
            {
                $this->print_pretty_array($results);
            }
            
        }
        else
        {
            // multi word search
            // break apart by space     
            $words = explode(" ", $desc); // split apart by space
            // die(print_r($words));
            foreach ($words as $desc) {
                $query = "select NDB_No, Long_Desc from food_description where Long_Desc like '%$desc%' or Long_Desc like '%$desc' or Long_Desc like '$desc%'"; 
                $sth = $this->dbh->prepare($query);
                $sth->execute();
                $results = $sth->FetchAll(PDO::FETCH_ASSOC);
                if (count($results) > $_POST['show_max']) {
                    print '<p>there are more than ' . $_POST['show_max'] . ' matches (' . count($results) . ' found for description <b>' . $desc . '</b>).</p>';
                }
                else
                {
                    $this->print_pretty_array($results);
                }
            }
        }
    }

    /** to output organized arrays , instead of printing out a long line of what seems at first to be gibberish */
    public function print_pretty_array($myArray) {
        print '<pre>';
        print_r($myArray);
        print '</pre>'; 
    }
}
